package oracle.to.mysql.utils;

public class SqlUtils {
	
	public static final String ORACLE_URL = "jdbc:oracle:thin:@localhost:1521:orcl";// oracle连接串
	public static final String ORACLE_USER = "ccrc";// oracle用户名zongshhu
	public static final String ORACLE_PWD = "ccrc";// oracle密码
	public static final String MYSQL_DB = "jyt_cc";// mysql实例名
	
	private static final String my_user = "root";// mysql用户名
	private static final String my_pwd = "root";// mysql密码
	private static final String my_url = "jdbc:mysql://localhost:3306/" + MYSQL_DB + "?user=" + my_user +
			"&password=" + my_pwd + "&useUnicode=true&useSSL=false&characterEncoding=UTF8";
	
	private static String SQL = "SELECT * FROM "
			+ "(SELECT a.*, ROWNUM rn FROM (SELECT * FROM {table} where 1=1 order by {orderby} ) a)"
			+ " WHERE rn BETWEEN {rn} AND {rownum} ";
	private static String USER_SQL_E = "SELECT * FROM "
			+ "(SELECT a.*, ROWNUM rn FROM ("
			+ " select USERID, ACCOUNT, PASSWORD, USER_NAME, MAIL, PHONE, QUESTION, ANSWER, REGISTER_TIME, LOGIN_TIME, LOGIN_NUM, IS_LOCKED, LOCK_USER_ID, LOCK_USER_NAME, LOCK_TIME, LOCK_REASON, ISVALID_MAIL, VALID_MAIL_TIME,  ISVALID_PHONE, VALID_PHONE_TIME,  AAE014 ,ACB200 from sys_user_E "
			+ " where acb200 is not null and acb200 in("
			+ " select acb200 from cb20_cc where aab001 in(select aab001 from ab01_cc) )"
			+ " order by acb200 ) a)"
			+ " WHERE rn BETWEEN {rn} AND {rownum} ";
	private static String USER_SQL_P = "SELECT * FROM "
			+ "(SELECT a.*, ROWNUM rn FROM ("
			+ " select USERID, ACCOUNT, PASSWORD, USER_NAME, MAIL, PHONE, QUESTION, ANSWER, REGISTER_TIME, LOGIN_TIME, LOGIN_NUM, IS_LOCKED, LOCK_USER_ID, LOCK_USER_NAME, LOCK_TIME, LOCK_REASON, ISVALID_MAIL, VALID_MAIL_TIME,  ISVALID_PHONE, VALID_PHONE_TIME ,AAC001 from sys_user_P "
			+ " order by aac001 ) a)"
			+ " WHERE rn BETWEEN {rn} AND {rownum} ";
	
	public static String getSqlByUserE( int start, int end) {
		return USER_SQL_E.replace("{rn}", String.valueOf(start)).replace("{rownum}", String.valueOf(end));
	}
	public static String getSqlByUserP( int start, int end) {
		return USER_SQL_P.replace("{rn}", String.valueOf(start)).replace("{rownum}", String.valueOf(end));
	}
	
	public static String getSql(String table,String where, String ordeby, int start, int end) {
		String result = SQL.replace("{table}", table).replace("{orderby}", ordeby)
				.replace("{rn}", String.valueOf(start)).replace("{rownum}", String.valueOf(end));
		if(where != null && !"".equals(where)){
			result = result.replace("1=1", where);
		}
		return result;
	}
	
	public static String getMySqlUrl(){
		return my_url;
	}

}
